package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import main.DataTransferObject;
import main.MyConnection;

public class AccessesDAOImpl implements AccessesDAO {

	@Override
	public boolean createEntry(DataTransferObject inDTO) {
		UserDAO userDAO = new UserDAOImpl();   //TODO:  Replace with factory?
		LocationDAO locDAO = new LocationDAOImpl();
		AlgorithmDAO algDAO = new AlgorithmDAOImpl();
		
		userDAO.createOrUpdate(inDTO);
		locDAO.createOrUpdate(inDTO.getIp());
		algDAO.createOrUpdate(inDTO);
		
		int userID = userDAO.getUserID(inDTO.getUserName());
		int locID = locDAO.getLocationFromIP(inDTO.getIp()).getLocID();
		int algID = algDAO.getAlgorithmFromName(inDTO.getAlgorithmName()).getAlgID();
		
		try{
			Connection conn = MyConnection.getConnection();
			
			String sql = "SELECT * FROM Accesses WHERE " +
					"User_ID = ? AND Loc_ID = ? AND Alg_ID = ? AND " +
					"Time_Stamp = ?";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userID);
			ps.setInt(2, locID);
			ps.setInt(3, algID);
			Timestamp ts = null;
			if(inDTO.getTime() != null){
				ts = new Timestamp(inDTO.getTime().getTime().getTime());
			}
			ps.setTimestamp(4, ts);
			if(ps.execute()){
				ResultSet rs = ps.getResultSet();
				if(rs.next()){
					//This value has already been added, so do nothing.
				}
				else {
					//Since no value has been found, we need to insert into the database.
					sql = "INSERT INTO Accesses (User_ID,Loc_ID,Alg_ID,Time_Stamp) "
						+ "VALUES (?,?,?,?)";
	
					ps = conn.prepareStatement(sql);
					
					ps = conn.prepareStatement(sql);
					ps.setInt(1, userID);
					ps.setInt(2, locID);
					ps.setInt(3, algID);
					
					if(inDTO.getTime() != null){
						ts = new Timestamp(inDTO.getTime().getTime().getTime());
					}
					ps.setTimestamp(4, ts);
					ps.execute();
				}
			}	
		} catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	@Override
	public List<DataTransferObject> getAllData(){

		ArrayList<DataTransferObject> dtos = new ArrayList<DataTransferObject>();
		try{
			Connection conn = MyConnection.getConnection();
			
			String sql = "SELECT u.Username as Username, l.Loc_Country as Country, l.Loc_City as City, al.Alg_Name as Algorithm, " +
					"a.Time_Stamp as Date " +
					"FROM Accesses a, Location l, User u, Algorithm al " +
					"WHERE a.User_ID = u.User_ID AND a.Loc_ID = l.Loc_ID AND a.Alg_ID = al.Alg_ID";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps = conn.prepareStatement(sql);
			if(ps.execute()){
				ResultSet rs = ps.getResultSet();
				while(rs.next()){
					DataTransferObject dto = new DataTransferObject();
					dto.setAlgorithmName(rs.getString("Algorithm"));
					dto.setCountry(rs.getString("Country"));
					dto.setCity(rs.getString("City"));
					dto.setUserName(rs.getString("Username"));
					Timestamp ts = rs.getTimestamp("Date");
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(ts.getTime());
					dto.setTime(cal);
					dtos.add(dto);
				}
			}	
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return dtos;
	}
	
	@Override
	public List<DataTransferObject> getCountryCityAlgorithm(){

		ArrayList<DataTransferObject> dtos = new ArrayList<DataTransferObject>();
		try{
			Connection conn = MyConnection.getConnection();
			
			String sql = "SELECT u.Username as Username, l.Loc_Country as Country, l.Loc_City as City, al.Alg_Name as Algorithm, " +
					"a.Time_Stamp as Date " +
					"FROM Accesses a, Location l, User u, Algorithm al " +
					"WHERE a.User_ID = u.User_ID AND a.Loc_ID = l.Loc_ID AND a.Alg_ID = al.Alg_ID " +
					"ORDER BY l.Loc_Country, l.Loc_City, al.Alg_Name ASC";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps = conn.prepareStatement(sql);
			if(ps.execute()){
				ResultSet rs = ps.getResultSet();
				while(rs.next()){
					DataTransferObject dto = new DataTransferObject();
					dto.setAlgorithmName(rs.getString("Algorithm"));
					dto.setCountry(rs.getString("Country"));
					dto.setCity(rs.getString("City"));
					dto.setUserName(rs.getString("Username"));
					Timestamp ts = rs.getTimestamp("Date");
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(ts.getTime());
					dto.setTime(cal);
					dtos.add(dto);
				}
			}	
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return dtos;
	}
	
	@Override
	public List<DataTransferObject> getAlgorithmCountryCity(){

		ArrayList<DataTransferObject> dtos = new ArrayList<DataTransferObject>();
		try{
			Connection conn = MyConnection.getConnection();
			
			String sql = "SELECT u.Username as Username, l.Loc_Country as Country, l.Loc_City as City, al.Alg_Name as Algorithm, " +
					"a.Time_Stamp as Date " +
					"FROM Accesses a, Location l, User u, Algorithm al " +
					"WHERE a.User_ID = u.User_ID AND a.Loc_ID = l.Loc_ID AND a.Alg_ID = al.Alg_ID " +
					"ORDER BY al.Alg_Name, l.Loc_Country, l.Loc_City ASC";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps = conn.prepareStatement(sql);
			if(ps.execute()){
				ResultSet rs = ps.getResultSet();
				while(rs.next()){
					DataTransferObject dto = new DataTransferObject();
					dto.setAlgorithmName(rs.getString("Algorithm"));
					dto.setCountry(rs.getString("Country"));
					dto.setCity(rs.getString("City"));
					dto.setUserName(rs.getString("Username"));
					Timestamp ts = rs.getTimestamp("Date");
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(ts.getTime());
					dto.setTime(cal);
					dtos.add(dto);
				}
			}	
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return dtos;
	}
	
	@Override
	public List<DataTransferObject> getCountryCityUser(){

		ArrayList<DataTransferObject> dtos = new ArrayList<DataTransferObject>();
		try{
			Connection conn = MyConnection.getConnection();
			
			String sql = "SELECT u.Username as Username, l.Loc_Country as Country, l.Loc_City as City, al.Alg_Name as Algorithm, " +
					"a.Time_Stamp as Date " +
					"FROM Accesses a, Location l, User u, Algorithm al " +
					"WHERE a.User_ID = u.User_ID AND a.Loc_ID = l.Loc_ID AND a.Alg_ID = al.Alg_ID " +
					"ORDER BY l.Loc_Country, l.Loc_City, u.Username ASC";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps = conn.prepareStatement(sql);
			if(ps.execute()){
				ResultSet rs = ps.getResultSet();
				while(rs.next()){
					DataTransferObject dto = new DataTransferObject();
					dto.setAlgorithmName(rs.getString("Algorithm"));
					dto.setCountry(rs.getString("Country"));
					dto.setCity(rs.getString("City"));
					dto.setUserName(rs.getString("Username"));
					Timestamp ts = rs.getTimestamp("Date");
					Calendar cal = Calendar.getInstance();
					cal.setTimeInMillis(ts.getTime());
					dto.setTime(cal);
					dtos.add(dto);
				}
			}	
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return dtos;
	}
}
